<?php
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

//Set properties
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setWidth(20);

$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(23);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('dadada');
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

// Add some data
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setName('Times New Roman');
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:D1')->setCellValue("A1","Report Excel Pertanyaan Periode ".$tgl_awal." s/d ".$tgl_akhir);
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');

$objPHPExcel->setActiveSheetIndex(0)
            ->mergeCells('A3:A4')->setCellValue('A3','No')
			->mergeCells('B3:B4')->setCellValue('B3','Periode')
            ->mergeCells('C3:C4')->setCellValue('C3','Pertanyaan ke-')
			->mergeCells('D3:D4')->setCellValue('D3','Nilai');

/********************Hasil*******************/
$a = 4;
$objPHPExcel->setActiveSheetIndex(0)
		    ->mergeCells('A5:A8')->setCellValue('A5', "1")
		    ->mergeCells('B5:B8')->setCellValue('B5', "Hasil");
			$t_hasil = 0;
			$sangat_puas = 0;
			$puas = 0;
			$kurang_puas = 0;
			$tidak_puas = 0;
			
			if($laporan_hasil == NULL || $laporan_hasil == "")
			{
				echo "<script>
					alert('Data tidak ditemukan !');
					location.href='blank_page/r_periode';
				</script>";
			}
			else
			{
				foreach ($laporan_hasil as $row) {
					$t_hasil++;
					if( $row["HASIL"] >= 1 && $row["HASIL"] <= 1.75 ){
						$tidak_puas++;
					} elseif( $row["HASIL"] >= 1.76 && $row["HASIL"] <= 2.5 ) {
						$kurang_puas++;
					} elseif( $row["HASIL"] >= 2.51 && $row["HASIL"] <= 3.25 ) {
						$puas++;
					} else {
						$sangat_puas++;
					}
				}
				
				$objPHPExcel->setActiveSheetIndex(0)
								->setCellValue('C5', "Sangat Puas")
								->setCellValue('D5', round(($sangat_puas/$t_hasil*100),2) . "%")
								->setCellValue('C6', "Puas")
								->setCellValue('D6', round(($puas/$t_hasil*100),2) . "%")
								->setCellValue('C7', "Kurang Puas")
								->setCellValue('D7', round(($kurang_puas/$t_hasil*100),2) . "%")
								->setCellValue('C8', "Tidak Puas")
								->setCellValue('D8', round(($tidak_puas/$t_hasil*100),2) . "%");							
			}
/********************End*******************/


/********************Usia*******************/
$t_usia = 0;
$awal	= 9;
$a		= 8;
foreach ($laporan_usia as $row) {
	$t_usia	= $t_usia + $row["USIA"];
}
foreach ($laporan_usia as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('C'.$a, $row["range_usia"])
				->setCellValue('D'.$a, round(($row["USIA"]/$t_usia*100),2) . "%");
}
$objPHPExcel->setActiveSheetIndex(0)
		    ->mergeCells('A'.$awal.':A'.$a)->setCellValue('A9', "2")
		    ->mergeCells('B'.$awal.':B'.$a)->setCellValue('B9', "Kelompok Umur");
/********************End*******************/


/********************Pertanyaan Periode*******************/
$t_tahun = 0;
$awal 	 = 5;
$a 		 = 4;
foreach($tahun as $row) {
	$t_tahun = $t_tahun + $row["TAHUN"];
}
foreach($pertanyaan as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('C'.$a, $row["ID_PERTANYAAN"])
				->setCellValue('D'.$a, $row["HASIL"]);	
}
$objPHPExcel->setActiveSheetIndex(0)
    ->mergeCells('A'.($awal+1).':A'.($a))->setCellValue('A'.($awal+1), "No (masuk looping)")
    ->mergeCells('B'.($awal+1).':B'.($a))->setCellValue('B'.($awal+1), "Tahun (masuk looping)");
/********************End*******************/


// $objPHPExcel->getActiveSheet()->getStyle("A4:D".$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle("A4:D".$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Report_Pertanyaan_Periode_Excel');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Report_Pertanyaan_Periode_Excel.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;